IF EXISTS (
	   SELECT *
	   FROM   sys.objects
	   WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_PageGetCountsForDifferentStatus]')
			  AND TYPE IN (N'P' ,N'PC')
   )
	DROP PROCEDURE [dbo].[cms_sp_PageGetCountsForDifferentStatus]
GO
CREATE PROCEDURE [dbo].[cms_sp_PageGetCountsForDifferentStatus]
@SiteGroupId int = NULL,
@SiteId int = NULL,
@PageId INT = NULL,
@PageTypeParameter nvarchar(400) = NULL
AS
declare @PageNodeId hierarchyid
declare @PageLevel int
select 
	@PageNodeId = PageNodeId,
	@PageLevel = @PageNodeId.GetLevel()
from dbo.Pages
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and 
	EntityRevisions.EntityTypeParameter = @PageTypeParameter
where ((SiteGroupId is null and SiteId is null) or 
	(SiteGroupId = @SiteGroupId and SiteId is null) or
	(SiteGroupId = @SiteGroupId and SiteId = @SiteId)) and 
	(EntityRevisions.IsLatestRevision = 1) AND 
	EntityRevisions.IsDeleted = 0 and
	PageId = @PageId
select 
	COUNT(*) AS [All],
	SUM(case when IsPublished=1 then 1 else 0 end) AS [Published],
	SUM(case when IsPublished=0 then 1 else 0 end) AS [Drafts]
from dbo.Pages
inner join Templates t on t.TemplateId = Pages.TemplateId
left join(select ChildPages.PageId, ChildPages.PageNodeId
		  from dbo.Pages as ChildPages
		  inner join EntityRevisions on EntityRevisions.EntityId = ChildPages.PageId and
					 EntityRevisions.EntityTypeParameter = @PageTypeParameter and
					(EntityRevisions.IsLatestRevision = 1)
		  where	((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))					
		 ) as ParentPages on ParentPages.PageNodeId = Pages.PageNodeId.GetAncestor(1)
left join(select count(ChildPages.PageNodeId) as ChildCount, PageNodeId.GetAncestor(1) as PageNodeId
		  from dbo.Pages as ChildPages
		  inner join EntityRevisions on EntityRevisions.EntityId = ChildPages.PageId and
					 EntityRevisions.EntityTypeParameter = @PageTypeParameter and
					(EntityRevisions.IsLatestRevision = 1)
		  where	((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))					
		  group by PageNodeId.GetAncestor(1)
		  ) as ChildrenPages on ChildrenPages.PageNodeId = Pages.PageNodeId		
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and 
	EntityRevisions.EntityTypeParameter = @PageTypeParameter
left join [dbo].[Users] as Creators
	on Creators.UserId = Pages.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = Pages.LastUpdatedBy
where 
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId)) and 
	(EntityRevisions.IsLatestRevision = 1) and
	EntityRevisions.IsDeleted = 0 and
	Pages.PageNodeId.IsDescendantOf(@PageNodeId) = 1 AND (Pages.PageNodeId.GetLevel() >= @PageLevel)
go